#!/usr/bin/env python
# -*- coding:utf-8 -*-
'''
@FileName  :DBOperation.py
@desc      :
@Author    :Young
@IDE       :PyCharm
@Time      :2022-10-16 17:12
'''
import sqlite3
con = sqlite3.connect('./UrbanLightingDB.db')
cur = con.cursor()

def database_init():
    '''
    数据库初始化
    :return: None
    '''
    sql1 = 'create table if not exists UserTB(id integer primary key, user_name varchar(30), IDcard_number text, password varchar(30))'
    sql2 = 'create table if not exists LightTB(id integer primary key, current_data varchar(30), current_time text)'
    sql3 = 'create table if not exists HumitureTB(id integer primary key, current_data varchar(30), current_time text)'

    cur.execute(sql1)
    cur.execute(sql2)
    cur.execute(sql3)

def close_cursor():
    '''
    释放数据库资源
    :return: None
    '''
    cur.close()  # 关闭游标
    con.close()  # 关闭连接

#用户表相关操作
def addUser(userName, IDNumber,password):
    '''
    添加用户信息（用户和卡号的绑定）
    :param userName: 用户名称
    :param IDNumber: 用户ID
    :return:
    '''

    sql = 'insert into UserTB(`user_name`, `IDcard_number`, `password`) values (?,?,?)'
    try:
        cur.execute(sql, (userName, IDNumber, password))
        con.commit()
        print("当前用户信息创建成功！")
    except Exception as e:
        print(e)
        con.rollback()
        print("插入用户数据失败！")




def deleteUserByName(userName):
    '''
    通过用户名删除用户
    :param userName: 用户名
    :return: None
    '''
    sql = 'delete form UserTB where user_name=?'
    try:
        cur.execute(sql,(userName,))
        print("删除用户数据成功！")
    except Exception as e:
        print(e)
        con.rollback()
        print("删除用户数据失败！")


def deleteUserByIDNumber(IDNumber):
    '''
    通过用户名ID删除用户
    :param IDNumber: 用户ID
    :return: True | False
    '''
    sql = 'delete form UserTB where IDcard_number=?'
    try:
        cur.execute(sql, (IDNumber,))
        print("删除用户数据成功！")
    except Exception as e:
        print(e)
        con.rollback()
        print("删除用户数据失败！")


def selectUserByIDNumber(IDNumber, password):
    '''
    通过用户ID查询用户信息（查询用户是否存在）
    :param IDNumber: 用户ID
    :return: 查询成功返回用户信息，查询不成功返回None
    '''
    sql = "select * from UserTB where IDcard_number=? and password=?"
    try:
        cur.execute(sql, (IDNumber,password))
        result = cur.fetchone()
        print(result)
        userName = result[1]
        print("该用户存在！")
        return userName
    except:
        print("该用户不存在！")
        return None


def selectAllUser():
    '''
    查询返回所有用户信息
    :return: 所有用户信息
    '''
    sql = "select * from UserTB"
    try:
        cur.execute(sql)
        users = cur.fetchall()
        print("获取全部用户数据成功！")
        return users
    except:
        print("获取全部用户数据失败")
        return None

# 光照信息表相关操作

def addLightInfo(currentTime, currentData):
    '''
    将当前光照信息，时间信息加入数据库中
    :param currentTime:
    :param currentInfo:
    :return:
    '''
    sql = 'insert into LightTB(`current_data`,`current_time`) values (?, ?)'
    try:
        cur.execute(sql, (currentData, currentTime))
        con.commit()
        print("当前光照数据插入成功！")
    except Exception as e:
        print(e)
        con.rollback()
        print("插入光照数据失败！")

def selectAllLightInfo():
    '''
    查询返回所有光照信息，信息若为空返回None
    :return: 所有光照信息 | None
    '''
    sql = "select * from LightTB"
    try:
        cur.execute(sql)
        con.commit()
        allLightInfo = cur.fetchall()
        print("获取所有光照数据成功！")
        return allLightInfo
    except:
        print("获取光照数据失败！")
        return None

# 温湿度表相关操作
def addHumitureInfo(currentTime, currentData):
    '''
    将当前温度信息，时间信息加入数据库中
    :param currentTime:
    :param currentInfo:
    :return:
    '''
    sql = 'insert into HumitureTB(`current_data`,`current_time`) values (?, ?)'
    try:
        cur.execute(sql, (currentData, currentTime))
        con.commit()
        print("当前温度数据插入成功！")
    except Exception as e:
        print(e)
        con.rollback()
        print("插入温度数据失败！")

def selectAllHumitureInfo():
    '''
    查询返回所有温度信息，信息若为空返回None
    :return: 所有光照信息 | None
    '''
    sql = "select * from HumitureTB"
    try:
        cur.execute(sql)
        con.commit()
        allHumitureInfo = cur.fetchall()
        print("获取所有温度数据成功！")
        return allHumitureInfo
    except:
        print("获取温度数据失败！")
        return None


async def getLatestHumitureData():
    '''
    返回最新的温度数据
    :return:
    '''
    sql = "select * from HumitureTB"
    try:
        cur.execute(sql)
        con.commit()
        latestHumitureInfo = cur.fetchall()[-1][1]
        #print(latestHumitureInfo)
        print("获取最新温度数据成功！")
        return latestHumitureInfo
    except:
        print("获取最新温度数据失败！")
        return None

def getLatestLightData():
    '''
    返回最新的光照数据
    :return:
    '''
    sql = "select * from LightTB"
    try:
        cur.execute(sql)
        con.commit()
        latestLightInfo = cur.fetchall()[-1][1]
        print(latestLightInfo)
        print("获最新光照数据成功！")
        return latestLightInfo
    except:
        print("获取最新光照数据失败！")
        return None



#test
# if __name__ == '__main__':
#     database_init()
#     addUser("李明", "12775", "123456")
#     addUser("花花", "12475", "123456")
#     addUser("陈陈陈","123456","123456")
#     result = selectUserByIDNumber("123456", "123456")
#     print(result)
#     close_cursor()